"""
存储过程操作功能演示示例

该示例展示了SQLServerManager类的存储过程相关操作，包括存储过程的创建、执行、存在性检查
以及删除等功能。
"""
from modules.database.sqlserver_manager import SQLServerManager
import logging

# 配置日志
handler = logging.StreamHandler()
handler.setFormatter(logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s'))
logger = logging.getLogger('sqlserver_manager')
logger.addHandler(handler)
logger.setLevel(logging.INFO)

def demonstrate_stored_procedures():
    # 数据库连接参数
    db_config = {
        'server': 'localhost',
        'database': 'test_db',
        'username': 'sa',
        'password': 'your_password',
        'port': 1433
    }

    # 使用上下文管理器确保连接正确关闭
    with SQLServerManager(**db_config) as db:
        try:
            table_name = 'EmployeeProcedures'
            proc_name = 'GetEmployeesByDepartment'
            proc_name_with_params = 'UpdateEmployeeSalary'

            # 1. 准备演示环境
            logger.info("=== 准备演示环境 ===")
            # 清理可能存在的旧表和存储过程
            if db.is_table_exists(table_name):
                db.execute_non_query(f"DROP TABLE {table_name}")
            
            if db.is_stored_procedure_exists(proc_name):
                db.execute_non_query(f"DROP PROCEDURE {proc_name}")
            
            if db.is_stored_procedure_exists(proc_name_with_params):
                db.execute_non_query(f"DROP PROCEDURE {proc_name_with_params}")
            
            # 创建演示表
            logger.info(f"创建演示表 {table_name}...")
            db.execute_non_query(f"""
                CREATE TABLE {table_name} (
                    ID INT PRIMARY KEY IDENTITY(1,1),
                    Name NVARCHAR(50) NOT NULL,
                    Department NVARCHAR(50) NOT NULL,
                    Salary DECIMAL(10,2) NOT NULL
                )
            """)
            
            # 插入测试数据
            test_data = [
                {'Name': '张三', 'Department': 'Engineering', 'Salary': 80000},
                {'Name': '李四', 'Department': 'Engineering', 'Salary': 90000},
                {'Name': '王五', 'Department': 'Marketing', 'Salary': 75000},
                {'Name': '赵六', 'Department': 'HR', 'Salary': 65000}
            ]
            db.batch_insert(table_name, test_data)
            logger.info(f"插入 {len(test_data)} 条测试数据")

            # 2. 创建无参数存储过程
            logger.info("\n=== 创建无参数存储过程 ===")
            create_proc_sql = f"""
            CREATE PROCEDURE {proc_name}
            AS
            BEGIN
                SET NOCOUNT ON;
                SELECT * FROM {table_name} ORDER BY Salary DESC;
            END
            """
            db.execute_non_query(create_proc_sql)
            logger.info(f"存储过程 {proc_name} 创建成功")

            # 3. 检查存储过程是否存在
            logger.info("\n=== 检查存储过程是否存在 ===")
            exists = db.is_stored_procedure_exists(proc_name)
            logger.info(f"存储过程 {proc_name} {'存在' if exists else '不存在'}")

            # 4. 执行无参数存储过程
            logger.info("\n=== 执行无参数存储过程 ===")
            results = db.execute_stored_procedure(proc_name)
            if results:
                logger.info(f"存储过程返回 {len(results[0])} 条记录:")
                for row in results[0][:3]:  # 只显示前3条
                    logger.info(f"- {row['Name']} ({row['Department']}): {row['Salary']}")
            
            # 5. 创建带参数的存储过程
            logger.info("\n=== 创建带参数的存储过程 ===")
            create_param_proc_sql = f"""
            CREATE PROCEDURE {proc_name_with_params}
                @EmployeeID INT,
                @PercentageIncrease DECIMAL(5,2)
            AS
            BEGIN
                SET NOCOUNT ON;
                UPDATE {table_name}
                SET Salary = Salary * (1 + @PercentageIncrease / 100)
                WHERE ID = @EmployeeID;
                SELECT * FROM {table_name} WHERE ID = @EmployeeID;
            END
            """
            db.execute_non_query(create_param_proc_sql)
            logger.info(f"带参数存储过程 {proc_name_with_params} 创建成功")

            # 6. 执行带参数的存储过程
            logger.info("\n=== 执行带参数的存储过程 ===")
            employee_id = 1
            percentage = 10  # 10%
            results = db.execute_stored_procedure(proc_name_with_params, (employee_id, percentage))
            
            if results:
                logger.info(f"为ID={employee_id}的员工加薪{percentage}%，结果:")
                for row in results[0]:
                    logger.info(f"- {row['Name']}: 原薪资 -> {row['Salary']/(1+percentage/100):.2f}, 加薪后 -> {row['Salary']:.2f}")

        except Exception as e:
            logger.error(f"操作发生错误: {str(e)}", exc_info=True)
        finally:
            # 7. 清理演示环境
            logger.info("\n=== 清理演示环境 ===")
            db.execute_non_query(f"DROP TABLE IF EXISTS {table_name}")
            db.execute_non_query(f"DROP PROCEDURE IF EXISTS {proc_name}")
            db.execute_non_query(f"DROP PROCEDURE IF EXISTS {proc_name_with_params}")
            logger.info("演示环境清理完成")

if __name__ == "__main__":
    demonstrate_stored_procedures()